﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace Pr0ject0.Models
{
    public class PreviousJobDB
    {
        private string connection;
        private List<PreviousJobModel> previous;
        public List<PreviousJobModel> PreviousJobsList { get { return new List<PreviousJobModel>(previous); } }

        public PreviousJobDB()
        {
            connection = "DB";
            update();
        }

        public PreviousJobDB(string connectionString)
        {
            connection = connectionString;
            update();
        }

        public void update()
        {
            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
            SqlCommand cmd = new SqlCommand("SELECT * FROM [PreviousJob]", conn);
            conn.Open();
            SqlDataReader reader = cmd.ExecuteReader();

            previous = new List<PreviousJobModel>();

            while (reader.Read())
            {
                PreviousJobModel model = new PreviousJobModel();
                model.ID = Convert.ToInt32(reader["Id"].ToString());
                model.employer = reader["employer"].ToString();
                model.address = reader["address"].ToString();
                model.city = reader["city"].ToString();
                model.state = reader["state"].ToString();
                model.phone = Convert.ToInt64(reader["phone"].ToString());
                model.startDate = DateTime.Parse(reader["startDate"].ToString());
                model.endDate = DateTime.Parse(reader["endDate"].ToString());
                model.startSalary = (float)Convert.ToDouble(reader["startSalary"].ToString());
                model.endSalary = (float)Convert.ToDouble(reader["endSalary"].ToString());
                model.supervisor = reader["supervisor"].ToString();
                model.position = reader["position"].ToString();
                model.reason = reader["reason"].ToString();
                model.responsibilities = reader["responsibilities"].ToString(); 
                previous.Add(model);
            }

            reader.Close();
            conn.Close();
        }

        public void Add(List<PreviousJobModel> list)
        {
            foreach (PreviousJobModel model in list)
            {
                Add(model);
            }
        }

        public void Add(PreviousJobModel model)
        {
            if (model.employer != null && model.employer != "")
            {
                string statement = "INSERT INTO PreviousJob VALUES({0}, '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', {8}," +
                    "{9}, '{10}', '{11}', '{12}', '{13}')";
                string insert;
                Object[] objects = { model.ID, model.employer, model.address, model.city, model.state, 
                                   model.phone, model.startDate, model.endDate, model.startSalary, model.endSalary,
                                    model.supervisor, model.position, model.reason, model.responsibilities};

                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings[connection].ToString());
                insert = string.Format(statement, objects);
                SqlCommand cmd = new SqlCommand(insert, conn);

                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();

                update();
            }
        }
    }
}